1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmStockRecord1
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("SELECT RTRIM(Stock_ID), [Date],RTRIM(Product.ProductCode),RTRIM(ProductName),Qty,Price,TotalAmount,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock,Product,Stock_Product where Supplier.ID=Stock.SupplierID and Product.PID=Stock_Product.ProductID and Stock.ST_ID=Stock_Product.StockID order by [Date]", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 End Sub
25
26 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
27 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
28 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
29 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
30 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
31 End If
32 Dim b As Brush = SystemBrushes.ControlText
33 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
34
35 End Sub
36 Sub Reset()
37 txtSupplierName.Text = ""
38 dtpDateFrom.Text = Today
39 dtpDateTo.Text = Today
40 DateTimePicker2.Text = Today
41 DateTimePicker1.Text = Today
42 Getdata()
43 End Sub
44 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
45 Reset()
46 End Sub
47
48 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
49 Me.Close()
50 End Sub
51
52 Private Sub txtSupplierName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSupplierName.TextChanged
53 Try
54 con = New SqlConnection(cs)
55 con.Open()
56 cmd = New SqlCommand("SELECT RTRIM(Stock_ID), [Date],RTRIM(Product.ProductCode),RTRIM(ProductName),Qty,Price,TotalAmount,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock,Product,Stock_Product where Supplier.ID=Stock.SupplierID and Product.PID=Stock_Product.ProductID and Stock.ST_ID=Stock_Product.StockID and [Name] like '%" & txtSupplierName.Text & "%' order by [Date]", con)
57 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
58 dgw.Rows.Clear()
59 While (rdr.Read() = True)
60 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12))
61 End While
62 con.Close()
63 Catch ex As Exception
64 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
65 End Try
66 End Sub
67
68
69 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
70 Dim rowsTotal, colsTotal As Short
71 Dim I, j, iC As Short
72 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
73 Dim xlApp As New Excel.Application
74 Try
75 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
76 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
77 xlApp.Visible = True
78
79 rowsTotal = dgw.RowCount
80 colsTotal = dgw.Columns.Count - 1
81 With excelWorksheet
82 .Cells.Select()
83 .Cells.Delete()
84 For iC = 0 To colsTotal
85 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
86 Next
87 For I = 0 To rowsTotal - 1
88 For j = 0 To colsTotal
89 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
90 Next j
91 Next I
92 .Rows("1:1").Font.FontStyle = "Bold"
93 .Rows("1:1").Font.Size = 12
94
95 .Cells.Columns.AutoFit()
96 .Cells.Select()
97 .Cells.EntireColumn.AutoFit()
98 .Cells(1, 1).Select()
99 End With
100 Catch ex As Exception
101 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
102 Finally
103 'RELEASE ALLOACTED RESOURCES
104 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
105 xlApp = Nothing
106 End Try
107 End Sub
108
109 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
110 Try
111 con = New SqlConnection(cs)
112 con.Open()
113 cmd = New SqlCommand("SELECT RTRIM(Stock_ID), [Date],RTRIM(Product.ProductCode),RTRIM(ProductName),Qty,Price,TotalAmount,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock,Product,Stock_Product where Supplier.ID=Stock.SupplierID and Product.PID=Stock_Product.ProductID and Stock.ST_ID=Stock_Product.StockID and [Date] between @d1 and @d2 order by [Date]", con)
114 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
115 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
116 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
117 dgw.Rows.Clear()
118 While (rdr.Read() = True)
119 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12))
120 End While
121 con.Close()
122 Catch ex As Exception
123 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
124 End Try
125 End Sub
126
127 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
128 Try
129 con = New SqlConnection(cs)
130 con.Open()
131 cmd = New SqlCommand("SELECT RTRIM(Stock_ID), [Date],RTRIM(Product.ProductCode),RTRIM(ProductName),Qty,Price,TotalAmount,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock,Product,Stock_Product where Supplier.ID=Stock.SupplierID and Product.PID=Stock_Product.ProductID and Stock.ST_ID=Stock_Product.StockID and [Date] between @d1 and @d2 and PaymentDue > 0 order by [Date]", con)
132 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
133 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value
134 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
135 dgw.Rows.Clear()
136 While (rdr.Read() = True)
137 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12))
138 End While
139 con.Close()
140 Catch ex As Exception
141 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
142 End Try
143 End Sub
144 End Class
